package com.yyyzzz.DAO.impl;

import com.yyyzzz.DAO.studentDao;
import com.yyyzzz.bean.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class studentDaoimpl implements studentDao {
    Course course = null;
    private Connection conn;
    private Statement stmt;
    private PreparedStatement ps;
    ResultSet resultSet;

    @Override
    public Student selectAdminByNameAndPwd(Student student) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/yyyzzz?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai";
        String name="root";
        String pwd="123456";

        try {
            Class.forName(driverClass); //加载驱动
            Connection conn = DriverManager.getConnection(url, name, pwd);// 创建数据库连接
            String sql = "select * from student where stuNo=? and stuPwd=?";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, student.getStuNo());
            pstm.setString(2, student.getStuPwd());
            ResultSet rs = pstm.executeQuery();
            Student student1 = null;
            if(rs.next()) {
                student1=new Student();
                student1.setStuNo(rs.getString(1));
                student1.setStuPwd(rs.getString(2));
            }
            rs.close();
            pstm.close();
            conn.close();
            return student1;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    @Override
    public int insterstudent(Student student) {

        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";
        try {
            Class.forName(driverClass);
            Connection conn = DriverManager.getConnection(url, name, pwd);
            String sql = "insert into student(stuNo,stuPwd,stuName,stuClass,stuMajor,stuEmail,stutelphone)"+"values(?,?,?,?,?,?,?)";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, student.getStuNo());
            pstm.setString(2, student.getStuPwd());
            pstm.setString(3, student.getStuName());
            pstm.setString(4, student.getStuClass());
            pstm.setString(5,student.getStuMajor());
            pstm.setString(6, student.getStuEmail());
            pstm.setString(7, student.getStutelphone());
            int rs = pstm.executeUpdate();
            conn.close();
            pstm.close();
            return rs;
        }catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;

    }
    public Student getStudentById (String stuNo) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url , username , password);
        System.out.println (connection);
        PreparedStatement preparedStatement = connection.prepareStatement ("select * from student where stuNo=?;");
        preparedStatement.setString(1, stuNo);
        preparedStatement.executeQuery();
        ResultSet rs = preparedStatement.getResultSet();
        while(rs.next()){
            String stuno = rs.getString ("stuNo");
            String stuname = rs.getString ("stuName");
            String stuclass = rs.getString("stuClass");
            String stumajor = rs.getString("stuMajor");
            String stuemail = rs.getString("stuEmail");
            String stutelphone =rs.getString ("stutelphone");
            System.out.println("学号："+rs.getObject("stuNo"));
            System.out.println("学生姓名："+rs.getObject("stuName"));
            System.out.println("班级："+rs.getObject("stuClass"));
            System.out.println("专业："+rs.getObject("stuMajor"));
            System.out.println("邮箱："+rs.getObject("stuEmail"));
            System.out.println("电话号码"+rs.getObject("stutelphone"));
            Student student = new Student (stuno , stuname , stuclass , stumajor , stuemail,stutelphone);
            System.out.println (student);
            return student;
        }
        rs.close();
        preparedStatement.close();
        connection.close ();
        return null;
    }
    @Override
    public List<Student> selectAll() {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass); //加载驱动
           Connection conn = DriverManager.getConnection(url, name, pwd);//配置
            String sql = "select * from student";

           PreparedStatement pstm = conn.prepareStatement(sql);
            resultSet = pstm.executeQuery();

            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()) {
                Student student = new Student();
                student.setStuNo(resultSet.getString("stuNo"));
                student.setStuPwd(resultSet.getString("stuPwd"));
                student.setStuName(resultSet.getString("stuName"));
                student.setStuClass(resultSet.getString("stuClass"));
                student.setStuMajor(resultSet.getString("stuMajor"));
                student.setStuEmail(resultSet.getString("stuEmail"));
                student.setStutelphone(resultSet.getString("stutelphone"));
                studentList.add(student);
            }
            for (Student student : studentList) {
                System.out.println(student);
            }

            pstm.close();
            conn.close();
            return studentList;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    //修改课程
    @Override
    public int updateStudent(Student student) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            Connection conn = DriverManager.getConnection(url, name, pwd);
            String sql = "update student set stuNo=?,stuName=?,stuClass=?,stuMajor=?,stuEmail=?,stutelphone=? where stuNo=" + student.getStuNo() + "";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, student.getStuNo());
            ps.setString(2, student.getStuName());
            ps.setString(3, student.getStuClass());
            ps.setString(4, student.getStuMajor());
            ps.setString(5,student.getStuEmail());
            ps.setString(6,student.getStutelphone());
            int row = ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public List selectOne(String stuno) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";


        try {
            Class.forName(driverClass);
            Connection conn = DriverManager.getConnection(url, name, pwd);
            String sql = "select * from student where stuNo=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, stuno);
            resultSet = ps.executeQuery();
            List<Student> studentList = new ArrayList<>();

            if (resultSet.next()) {
                Student student = new Student();
                student.setStuNo(resultSet.getString("stuNo"));
                student.setStuPwd(resultSet.getString("stuPwd"));
                student.setStuName(resultSet.getString("stuName"));
                student.setStuClass(resultSet.getString("stuClass"));
                student.setStuMajor(resultSet.getString("stuMajor"));
                studentList.add(student);

            }

            conn.close();
            ps.close();
            return studentList;

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public List<StudentCourse> selectAllCourse() {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass); //加载驱动
            Connection conn = DriverManager.getConnection(url, name, pwd);//配置
            String sql = "select * from studentcourse";

            PreparedStatement pstm = conn.prepareStatement(sql);
            resultSet = pstm.executeQuery();

            List<StudentCourse> studentCourseList = new ArrayList<>();
            while (resultSet.next()) {
                StudentCourse studentCourse = new StudentCourse();
                studentCourse.setCourseId(resultSet.getString("courseid"));
                studentCourse.setCourseName(resultSet.getString("coursename"));
                studentCourse.setCourseTeacher(resultSet.getString("courseteacher"));
                studentCourse.setClassroom(resultSet.getString("classroom"));
                studentCourse.setCredit(resultSet.getDouble("credit"));
                    studentCourseList.add(studentCourse);
            }
            for (StudentCourse studentCourse : studentCourseList) {
                System.out.println(studentCourse);
            }

            pstm.close();
            conn.close();
            return studentCourseList;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int insertCourse(StudentCourse studentCourse) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql = "insert into studentcourse(courseid,coursename,courseteacher,classroom,credit) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, studentCourse.getCourseId());
            ps.setString(2, studentCourse.getCourseName());
            ps.setString(3, studentCourse.getCourseTeacher());
            ps.setString(4, studentCourse.getClassroom());
            ps.setDouble(5, studentCourse.getCredit());
            int row = ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public int deleteStuCourseByid(String courseid){
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql="delete from studentcourse where courseid='"+courseid+"'";
            ps=conn.prepareStatement(sql);
            int row=ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }


}
